package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc6ExpenseOtherBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.UserInfo;
import gov.nhso.prov.acc.utils.Utility;

public class ACC60312 extends Utility{
	public String url = "/apps/acc6/ACC60312A.jsp";

	public ACC60312(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if (job.equals("init")) {
			this.init(request);
		} else if (job.equals("add")) {
			this.add(request);
		} else if (job.equals("save")){
			this.save(request);
		} else if (job.equals("edit")) {
			this.edit(request);
		} else if (job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		} else if (job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("view")){
			this.view(request);
		} else if (job.equals("confirm")) {
			this.confirm(request);
		} else if(job.equals("del")){
			this.del(request);
		}
	}
	
	private String init(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "save");
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60312B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String save(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ExpenseOtherBean eob = new Acc6ExpenseOtherBean();
			this.beanUtil().copyRequest2Bean(request, eob);
			eob.setDocNo(this.genCode(dao).getExpenseOtherDocNo(eob.getBudgetYear(), eob.getProvinceId(), "P"));
			eob.setExpenseType("P");
			eob.setExpenseDate(this.dateUtil().changeDateToDB(eob.getExpenseDate()));
			eob.setExpenseAmount(eob.getExpenseAmount().replaceAll(",", ""));
			eob.setCreatedBy(ui.getUserId());
			eob.setUpdatedBy(ui.getUserId());
			dao.insert(eob);
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.view(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String edit(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ExpenseOtherBean eob = new Acc6ExpenseOtherBean();
			this.beanUtil().copyRequest2Bean(request, eob);
			eob.setExpenseDate(this.dateUtil().changeDateToDB(eob.getExpenseDate()));
			eob.setExpenseAmount(eob.getExpenseAmount().replaceAll(",", ""));
			eob.setUpdatedBy(ui.getUserId());
			eob.setUpdatedDate(this.sysDate);
			dao.update(eob);
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.view(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String view(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc6ExpenseOtherBean eob = new Acc6ExpenseOtherBean();
			eob.setDocNo(docNo);
			eob.setBudgetYear(budgetYear);
			eob.setProvinceId(provinceId);
			dao.select(eob);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("listAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(budgetYear));
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("job", "edit");
			request.setAttribute("eob", eob);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60312B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ExpenseOtherBean ebo = new Acc6ExpenseOtherBean();
			this.beanUtil().copyRequest2Bean(request, ebo);
			this.beanUtil().copyRequest2Bean(request, pageBean);
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			
			String condition = "";
			
			if(!dao.chkData(startDate).equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}			
			if(!dao.chkData(endDate).equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			ebo.setActive("Y");
			this.beanUtil().copyRequest2Bean(request, pageBean);
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(ebo, condition, pageBean);
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("provinceId", ebo.getProvinceId());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String confirm(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			String ts = "";
			ts = this.dateUtil().getCurrentTimeStamp2DB();
			Acc6ExpenseOtherBean eob = new Acc6ExpenseOtherBean();
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			this.beanUtil().copyRequest2Bean(request, eob);
			Acc6ExpenseOtherBean bean = new Acc6ExpenseOtherBean();
			bean.setDocNo(eob.getDocNo());
			bean.setBudgetYear(eob.getBudgetYear());
			bean.setProvinceId(eob.getProvinceId());
			dao.select(bean);
			if (bean.getExpenseStatus().equals("P")) {
				String expenseDate = this.dateUtil().changeDateToDB(eob.getExpenseDate()); 
				eob.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
				eob.setExpenseDate(expenseDate);
				eob.setExpenseAmount(eob.getExpenseAmount().replaceAll(",", ""));
				eob.setExpenseStatus("Y");
				eob.setUpdatedBy(ui.getUserId());
				eob.setUpdatedDate(this.sysDate);
				dao.update(eob);
				
				transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
				transactionBean.setTransactionType("O");
				transactionBean.setRefNo(eob.getRefNo());
				transactionBean.setBudgetYear(eob.getBudgetYear());
				transactionBean.setAllocateCode(eob.getAllocateCode());
				transactionBean.setProvinceId(eob.getProvinceId());
				transactionBean.setExpenseDate(expenseDate);
				transactionBean.setExpenseAmount(eob.getExpenseAmount().replaceAll(",", ""));
				transactionBean.setTransactionType("P");
				transactionBean.setReasonCode("00");
				dao.insert(transactionBean);
				dao.printMessage();
				this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			} else {
				this.view(request);
				this.message(request).setMsg("E", "ไม่สามารถยืนยันได้ สถานะไม่ได้อยู่เตรียมดำเนินการ กรุณาตรวจสอบ");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.view(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String del(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc6ExpenseOtherBean eob = new Acc6ExpenseOtherBean();
			eob.setDocNo(docNo);
			eob.setBudgetYear(budgetYear);
			eob.setProvinceId(provinceId);
			eob.setActive("N");
			eob.setUpdatedBy(ui.getUserId());
			eob.setUpdatedDate(this.sysDate);
			dao.update(eob);
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				request.setAttribute("docNo", "");
				request.setAttribute("budgetYear", "");
				request.setAttribute("provinceId", "");
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
